'----------------------------------------------
' Name: Ali Karbassi & Chris Hanegraaf
' Date: Date the project is created
' Description: Assignment 5
'              Module DataModule
'----------------------------------------------
Imports UWPCS3340
Imports Microsoft.Win32.RegistryValueKind

Module DataModule

   ' Database Registry key
   Friend dbkey As Microsoft.Win32.RegistryKey

	' Location of the Database
	Dim DBLocation As String

	' Connection String
   Const connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="

	' Forms
	Friend frmEmployees As New UWPCS3340.frmGrid
	Friend frmCustomers As New UWPCS3340.frmGrid
	Friend frmProducts As New UWPCS3340.frmGrid

   ' One connection to one database file
   Public conProg5 As New OleDb.OleDbConnection

	' DataTables
	Public tblEmp As New System.Data.DataTable
	Public tblCust As New System.Data.DataTable
   Public tblProd As New System.Data.DataTable
   Public tblOrders As New System.Data.DataTable
   Public tblOrderDet As New System.Data.DataTable

	' Commands
	Public cmdEmp As New OleDb.OleDbCommand
	Public cmdCust As New OleDb.OleDbCommand
   Public cmdProd As New OleDb.OleDbCommand
   Public cmdOrders As New OleDb.OleDbCommand
   Public cmdOrderDet As New OleDb.OleDbCommand

	' Adapters
	Friend empAdapter As New OleDb.OleDbDataAdapter
	Friend custAdapter As New OleDb.OleDbDataAdapter
   Friend prodAdapter As New OleDb.OleDbDataAdapter
   Friend ordersAdapter As New OleDb.OleDbDataAdapter
   Friend orderDetAdapter As New OleDb.OleDbDataAdapter

	' Builders
	Friend empBuilder As OleDb.OleDbCommandBuilder
	Friend custBuilder As OleDb.OleDbCommandBuilder
   Friend prodBuilder As OleDb.OleDbCommandBuilder
   Friend ordersBuilder As OleDb.OleDbCommandBuilder
   Friend orderDetBuilder As OleDb.OleDbCommandBuilder

   'Views
   Friend ordersView As New DataView
   Friend orderDetView As New DataView

   ''' <summary>
   ''' Beginning of the program. Calls subs that make the connection and sets up the table. 
   ''' Then creates the new form
   ''' </summary>
   ''' <remarks></remarks>
	Sub main()

		makeConnection()
		setUpTable()

      frmEmployees.Text = "Prog 5 (Ali Karbassi & Chris Hanegraaf) - Table Employees"
		frmEmployees.MainForm = frmUser
      frmEmployees.Table = tblEmp
      frmEmployees.Adapter = empAdapter


      frmCustomers.Text = "Prog 5 (Ali Karbassi & Chris Hanegraaf) - Table Customers"
		frmCustomers.MainForm = frmUser
      frmCustomers.Table = tblCust
      frmCustomers.Adapter = custAdapter

      frmProducts.Text = "Prog 5 (Ali Karbassi & Chris Hanegraaf) - Table Products"
		frmProducts.MainForm = frmUser
      frmProducts.Table = tblProd
      frmProducts.Adapter = prodAdapter

		Application.Run(frmUser)

	End Sub

   ''' <summary>
   ''' Makes the connection. Stores the location in registry.
   ''' </summary>
   ''' <remarks></remarks>
	Sub makeConnection()

      Dim connected As Boolean = False

      Try

         dbkey = Microsoft.Win32.Registry.LocalMachine.CreateSubKey("Software\UWPCS3340S2007")
         DBLocation = dbkey.GetValue("Software\UWPCS3340S2007", "")

         conProg5.ConnectionString = connString & DBLocation

         conProg5.Open()

      Catch ex As Exception

         openDialog()
         dbkey.SetValue("Software\UWPCS3340S2007", DBLocation, [String])
         conProg5.ConnectionString = connString & DBLocation

      End Try

      

   End Sub

   ''' <summary>
   ''' Sets up all the tables.
   ''' </summary>
   ''' <remarks></remarks>
	Friend Sub setUpTable()

      Dim connected As Boolean = False

		' Employees
		cmdEmp.CommandType = CommandType.Text
      cmdEmp.CommandText = "SELECT * FROM Employees ORDER BY EmployeeID ASC"
		cmdEmp.Connection = conProg5
      empAdapter.SelectCommand = cmdEmp
		empBuilder = New OleDb.OleDbCommandBuilder(empAdapter)

		' Customers
		cmdCust.CommandType = CommandType.Text
      cmdCust.CommandText = "SELECT * FROM Customers ORDER BY AccountNo ASC"
		cmdCust.Connection = conProg5
      custAdapter.SelectCommand = cmdCust
		custBuilder = New OleDb.OleDbCommandBuilder(custAdapter)

		' Products
		cmdProd.CommandType = CommandType.Text
      cmdProd.CommandText = "SELECT * FROM Products ORDER BY ProductName ASC"
      cmdProd.Connection = conProg5
      prodAdapter.SelectCommand = cmdProd
      prodBuilder = New OleDb.OleDbCommandBuilder(prodAdapter)

      ' Orders
      cmdOrders.CommandType = CommandType.Text
      cmdOrders.CommandText = "Select * from Orders ORDER BY CustomerID DESC"
      cmdOrders.Connection = conProg5
      ordersAdapter.SelectCommand = cmdOrders
      ordersBuilder = New OleDb.OleDbCommandBuilder(ordersAdapter)

      ' Order Details
      cmdOrderDet.CommandType = CommandType.Text
      cmdOrderDet.CommandText = "Select * from OrderDetails ORDER BY ProductID ASC"
      cmdOrderDet.Connection = conProg5
      orderDetAdapter.SelectCommand = cmdOrderDet
      orderDetBuilder = New OleDb.OleDbCommandBuilder(orderDetAdapter)


      While connected = False

         Try
            ' Fill tables
            empAdapter.Fill(tblEmp)
            custAdapter.Fill(tblCust)
            prodAdapter.Fill(tblProd)
            orderDetAdapter.Fill(tblOrderDet)
            ordersAdapter.Fill(tblOrders)
            connected = True

         Catch ex As Exception

            openDialog()
            conProg5.ConnectionString = connString & DBLocation
            dbkey.SetValue("Software\UWPCS3340S2007", DBLocation, [String])

         End Try

      End While

	End Sub

	''' <summary>
	''' Opens the file dialog.
	''' </summary>
	''' <remarks></remarks>
	Private Sub openDialog()

		Dim openDB As New OpenFileDialog
		openDB.Filter = "MS Access Database (*.mdb)|*.mdb"

		If openDB.ShowDialog() = Windows.Forms.DialogResult.OK Then

			dblocation = openDB.FileName

		Else

			MsgBox("No Database Selected!")
			End
			Application.Exit()

		End If

	End Sub

End Module
